﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using Card.Entity;
using System.Data.SqlClient;
using Card.DataBase;

namespace Card.DAL
{
    /// <summary>
    /// 数据访问类AREA。
    /// </summary>
    internal class AREA : DataBase.BaseDAL
    {

        #region  成员方法
        /// <summary>
        /// 是否存在该记录
        /// </summary>
        public virtual bool Exists(int ID, Security.Passport passport)
        {
            string cmdText = @"SELECT COUNT(1) FROM AREA
											WHERE ID=@ID ";
            SqlParameter[] cmdParms = new SqlParameter[1];
            int i = -1;

            i++;
            cmdParms[i] = new SqlParameter("@ID", SqlDbType.Int, 4);
            cmdParms[i].Direction = ParameterDirection.Input;
            cmdParms[i].Value = ID;

            object obj = SqlHelper.ExecuteScalar(ConnectionString, CommandType.Text, cmdText, cmdParms);
            int result = 0;
            if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
            {
                result = 0;
            }
            else
            {
                result = int.Parse(obj.ToString());
            }
            return result == 0 ? false : true;
        }

        /// <summary>
        /// 获得数据列表
        /// </summary>
        public virtual DataSet GetList(string strWhere, Security.Passport passport)
        {
            string cmdText = @"SELECT 
											ID,NAME,PARENT_AREA_ID,AREA_TYPE,SORT,SITE_CODE,SITE_URL,IS_ACTIVE
										 FROM AREA ";
            if (strWhere.Trim() != "")
            {
                cmdText += " WHERE 1=1 " + strWhere;
            }

            return SqlHelper.ExecuteDataset(ConnectionString, CommandType.Text, cmdText);
        }


        /// <summary>
        /// 新增一条数据
        /// </summary>
        /// <param name="entity"></param>
        /// <returns></returns>
        public virtual bool Add(Area entity, Security.Passport passport)
        {
            string cmdText = @"INSERT INTO AREA(
														NAME,
														PARENT_AREA_ID,
														AREA_TYPE,
														SORT,
                                                        SITE_CODE,
														SITE_URL,
														IS_ACTIVE)
											VALUES(
														@NAME,
														@PARENT_AREA_ID,
														@AREA_TYPE,
														@SORT,
                                                        @SITE_CODE,
														@SITE_URL,
														@IS_ACTIVE)";
            SqlParameter[] cmdParms = new SqlParameter[7];
            int i = -1;

            i++;
            cmdParms[i] = new SqlParameter("@NAME", SqlDbType.NVarChar, 100);
            cmdParms[i].Direction = ParameterDirection.Input;
            cmdParms[i].Value = entity.Name;

            i++;
            cmdParms[i] = new SqlParameter("@PARENT_AREA_ID", SqlDbType.Int, 4);
            cmdParms[i].Direction = ParameterDirection.Input;
            cmdParms[i].Value = entity.ParentAreaID;

            i++;
            cmdParms[i] = new SqlParameter("@AREA_TYPE", SqlDbType.NVarChar, 50);
            cmdParms[i].Direction = ParameterDirection.Input;
            cmdParms[i].Value = entity.AreaType;

            i++;
            cmdParms[i] = new SqlParameter("@SORT", SqlDbType.Int, 4);
            cmdParms[i].Direction = ParameterDirection.Input;
            cmdParms[i].Value = entity.Sort;

            i++;
            cmdParms[i] = new SqlParameter("@SITE_CODE", SqlDbType.NVarChar, 50);
            cmdParms[i].Direction = ParameterDirection.Input;
            cmdParms[i].Value = entity.SiteCode;

            i++;
            cmdParms[i] = new SqlParameter("@SITE_URL", SqlDbType.NVarChar, 500);
            cmdParms[i].Direction = ParameterDirection.Input;
            cmdParms[i].Value = entity.SiteUrl;

            i++;
            cmdParms[i] = new SqlParameter("@IS_ACTIVE", SqlDbType.NVarChar, 1);
            cmdParms[i].Direction = ParameterDirection.Input;
            cmdParms[i].Value = entity.IsActive;

            

            int rowEffected = SqlHelper.ExecuteNonQuery(ConnectionString, CommandType.Text, cmdText, cmdParms);
            return rowEffected == 1 ? true : false;
        }
        /// <summary>
        /// 更新一条数据
        /// </summary>
        public virtual bool Update(Area entity, Security.Passport passport)
        {
            string cmdText = @"UPDATE AREA SET 
														NAME=@NAME,
														PARENT_AREA_ID=@PARENT_AREA_ID,
														AREA_TYPE=@AREA_TYPE,
														SORT=@SORT,
                                                        SITE_CODE=@SITE_CODE,
														SITE_URL=@SITE_URL,
														IS_ACTIVE=@IS_ACTIVE
											WHERE ID=@ID ";
            SqlParameter[] cmdParms = new SqlParameter[8];
            int i = -1;

            i++;
            cmdParms[i] = new SqlParameter("@ID", SqlDbType.Int, 4);
            cmdParms[i].Direction = ParameterDirection.Input;
            cmdParms[i].Value = entity.ID;

            i++;
            cmdParms[i] = new SqlParameter("@NAME", SqlDbType.NVarChar, 100);
            cmdParms[i].Direction = ParameterDirection.Input;
            cmdParms[i].Value = entity.Name;

            i++;
            cmdParms[i] = new SqlParameter("@PARENT_AREA_ID", SqlDbType.Int, 4);
            cmdParms[i].Direction = ParameterDirection.Input;
            cmdParms[i].Value = entity.ParentAreaID;

            i++;
            cmdParms[i] = new SqlParameter("@AREA_TYPE", SqlDbType.NVarChar, 50);
            cmdParms[i].Direction = ParameterDirection.Input;
            cmdParms[i].Value = entity.AreaType;

            i++;
            cmdParms[i] = new SqlParameter("@SORT", SqlDbType.Int, 4);
            cmdParms[i].Direction = ParameterDirection.Input;
            cmdParms[i].Value = entity.Sort;

            i++;
            cmdParms[i] = new SqlParameter("@SITE_CODE", SqlDbType.NVarChar, 50);
            cmdParms[i].Direction = ParameterDirection.Input;
            cmdParms[i].Value = entity.SiteCode;

            i++;
            cmdParms[i] = new SqlParameter("@SITE_URL", SqlDbType.NVarChar, 500);
            cmdParms[i].Direction = ParameterDirection.Input;
            cmdParms[i].Value = entity.SiteUrl;

            i++;
            cmdParms[i] = new SqlParameter("@IS_ACTIVE", SqlDbType.NVarChar, 1);
            cmdParms[i].Direction = ParameterDirection.Input;
            cmdParms[i].Value = entity.IsActive;

            int rowEffected = SqlHelper.ExecuteNonQuery(ConnectionString, CommandType.Text, cmdText, cmdParms);
            return rowEffected == 1 ? true : false;
        }


        /// <summary>
        /// 得到一个对象实体
        /// </summary>
        public virtual Area GetEntity(int ID, Security.Passport passport)
        {
            string cmdText = @"SELECT ID,NAME,PARENT_AREA_ID,AREA_TYPE,SORT,SITE_CODE,SITE_URL,IS_ACTIVE FROM AREA
											WHERE ID=@ID ";
            SqlParameter[] cmdParms = new SqlParameter[1];
            int i = -1;

            i++;
            cmdParms[i] = new SqlParameter("@ID", SqlDbType.Int, 4);
            cmdParms[i].Direction = ParameterDirection.Input;
            cmdParms[i].Value = ID;

            Entity.Area entity = new Entity.Area();
            DataSet ds = SqlHelper.ExecuteDataset(ConnectionString, CommandType.Text, cmdText, cmdParms);
            if (ds.Tables[0].Rows.Count > 0)
            {
                if (ds.Tables[0].Rows[0]["ID"].ToString() != "")
                {
                    entity.ID = int.Parse(ds.Tables[0].Rows[0]["ID"].ToString());
                }
                entity.Name = ds.Tables[0].Rows[0]["NAME"].ToString().Trim();
                if (ds.Tables[0].Rows[0]["PARENT_AREA_ID"].ToString() != "")
                {
                    entity.ParentAreaID = int.Parse(ds.Tables[0].Rows[0]["PARENT_AREA_ID"].ToString());
                }
                entity.AreaType = ds.Tables[0].Rows[0]["AREA_TYPE"].ToString().Trim();
                if (ds.Tables[0].Rows[0]["SORT"].ToString() != "")
                {
                    entity.Sort = int.Parse(ds.Tables[0].Rows[0]["SORT"].ToString());
                }
                entity.SiteCode = ds.Tables[0].Rows[0]["SITE_CODE"].ToString().Trim();
                entity.SiteUrl = ds.Tables[0].Rows[0]["SITE_URL"].ToString().Trim();
                entity.IsActive = ds.Tables[0].Rows[0]["IS_ACTIVE"].ToString().Trim();
                return entity;
            }
            else
            {
                return null;
            }
        }



        /// <summary>
        /// 删除一条数据
        /// </summary>
        public virtual bool Delete(int ID, Security.Passport passport)
        {
            string cmdText = @"DELETE AREA
											WHERE ID=@ID ";
            SqlParameter[] cmdParms = new SqlParameter[1];
            int i = -1;

            i++;
            cmdParms[i] = new SqlParameter("@ID", SqlDbType.Int, 4);
            cmdParms[i].Direction = ParameterDirection.Input;
            cmdParms[i].Value = ID;

            int rowEffected = SqlHelper.ExecuteNonQuery(ConnectionString, CommandType.Text, cmdText, cmdParms);
            return rowEffected == 1 ? true : false;
        }

        protected Entity.Area GetEntity(DataRow drwData)
        {
            Entity.Area entity = new Area();

            if (drwData["ID"].ToString() != "")
            {
                entity.ID = int.Parse(drwData["ID"].ToString());
            }
            entity.Name = drwData["NAME"].ToString().Trim();
            entity.AreaType = drwData["AREA_TYPE"].ToString().Trim();
            if (drwData["PARENT_AREA_ID"].ToString() != "")
            {
                entity.ParentAreaID = int.Parse(drwData["PARENT_AREA_ID"].ToString());
            }
            if (drwData["SORT"].ToString() != "")
            {
                entity.Sort = int.Parse(drwData["SORT"].ToString());
            }
            entity.SiteCode = drwData["SITE_CODE"].ToString().Trim();
            entity.SiteUrl = drwData["SITE_URL"].ToString().Trim();
            entity.IsActive = drwData["IS_ACTIVE"].ToString().Trim();
            return entity;
        }
        #endregion  成员方法

        #region IAREA 成员
        /// <summary>
        /// 得到所有地区
        /// </summary>
        /// <returns></returns>
        public List<Entity.Area> GetAllArea()
        {
            DataSet ds = this.GetList(string.Empty, null);
            List<Entity.Area> lstArea = new List<Entity.Area>();
            if (ds.Tables[0].Rows.Count > 0)
            {
                foreach (DataRow drData in ds.Tables[0].Rows)
                {
                    lstArea.Add(this.GetEntity(drData));
                }
            }

            return lstArea;
        }

        public List<Entity.Area> GetListByParentID(int parentID, Security.Passport passport)
        {
            string cmdText = @"SELECT ID,NAME,AREA_TYPE,PARENT_AREA_ID,SORT,SITE_CODE,SITE_URL,IS_ACTIVE FROM AREA
											WHERE PARENT_AREA_ID=@PARENT_AREA_ID ORDER BY SORT";

            SqlParameter[] cmdParms = new SqlParameter[1];
            int i = -1;

            i++;
            cmdParms[i] = new SqlParameter("@PARENT_AREA_ID", SqlDbType.Int, 4);
            cmdParms[i].Direction = ParameterDirection.Input;
            cmdParms[i].Value = parentID;

            DataSet ds = SqlHelper.ExecuteDataset(ConnectionString, CommandType.Text, cmdText, cmdParms);
            List<Entity.Area> lstArea = new List<Entity.Area>();
            if (ds.Tables[0].Rows.Count > 0)
            {
                foreach (DataRow drData in ds.Tables[0].Rows)
                {
                    lstArea.Add(this.GetEntity(drData));
                }
            }

            return lstArea;
        }


        /// <summary>
        /// 通过ID得到所有父节点
        /// </summary>
        /// <param name="areaID"></param>
        /// <param name="passport"></param>
        /// <returns></returns>
        public List<Entity.Area> GetParentListByID(int areaID, Security.Passport passport)
        {
            string cmdText = @"WITH CHILDREN AS(
                                SELECT * FROM AREA WHERE ID=@ID
                                 UNION ALL 
	                                SELECT AREA.* FROM CHILDREN  
	                                INNER JOIN AREA ON CHILDREN.PARENT_AREA_ID=AREA.ID
                                )

                                SELECT * FROM CHILDREN ORDER BY SORT";

            SqlParameter[] cmdParms = new SqlParameter[1];
            int i = -1;

            i++;
            cmdParms[i] = new SqlParameter("@ID", SqlDbType.Int, 4);
            cmdParms[i].Direction = ParameterDirection.Input;
            cmdParms[i].Value = areaID;

            DataSet ds = SqlHelper.ExecuteDataset(ConnectionString, CommandType.Text, cmdText, cmdParms);
            List<Entity.Area> lstArea = new List<Entity.Area>();
            if (ds.Tables[0].Rows.Count > 0)
            {
                foreach (DataRow drData in ds.Tables[0].Rows)
                {
                    lstArea.Add(this.GetEntity(drData));
                }
            }

            return lstArea;
        }

        /// <summary>
        /// 得到兄弟地区
        /// </summary>
        /// <param name="areaID"></param>
        /// <param name="passport"></param>
        /// <returns></returns>
        public List<Entity.Area> GetBrotherAreaByID(int areaID, Security.Passport passport)
        {
            string cmdText = @"SELECT ID,NAME,AREA_TYPE,PARENT_AREA_ID,SORT,SITE_CODE,SITE_URL,IS_ACTIVE FROM AREA
											WHERE PARENT_AREA_ID IN (SELECT PARENT_AREA_ID FROM AREA WHERE ID=@ID) ORDER BY SORT";

            SqlParameter[] cmdParms = new SqlParameter[1];
            int i = -1;

            i++;
            cmdParms[i] = new SqlParameter("@ID", SqlDbType.Int, 4);
            cmdParms[i].Direction = ParameterDirection.Input;
            cmdParms[i].Value = areaID;

            DataSet ds = SqlHelper.ExecuteDataset(ConnectionString, CommandType.Text, cmdText, cmdParms);
            List<Entity.Area> lstArea = new List<Entity.Area>();
            if (ds.Tables[0].Rows.Count > 0)
            {
                foreach (DataRow drData in ds.Tables[0].Rows)
                {
                    lstArea.Add(this.GetEntity(drData));
                }
            }

            return lstArea;
        }
        /// <summary>
        /// 获得省市区名称
        /// </summary>
        /// <param name="areaID"></param>
        /// <param name="passport"></param>
        /// <returns></returns>
        public DataTable GetAreaNameByID(int areaID, Security.Passport passport)
        {
            string cmdText = @"SELECT A.[NAME] AS PROVINCENAME,B.[NAME] AS CITYNAME,C.[NAME] AS AREANAME
FROM AREA A,AREA B,AREA C
WHERE C.PARENT_AREA_ID=B.ID AND B.PARENT_AREA_ID=A.ID AND C.ID=@ID";
            SqlParameter[] cmdParms = new SqlParameter[1];
            int i = -1;

            i++;
            cmdParms[i] = new SqlParameter("@ID", SqlDbType.Int, 4);
            cmdParms[i].Direction = ParameterDirection.Input;
            cmdParms[i].Value = areaID;

            return SqlHelper.ExecuteDataset(ConnectionString, CommandType.Text, cmdText, cmdParms).Tables[0];
        }

        /// <summary>
        /// 是否有子节点
        /// </summary>
        /// <param name="parentID"></param>
        /// <param name="passport"></param>
        /// <returns></returns>
        public bool ExistsChildren(int parentID, Security.Passport passport)
        {
            string cmdText = @"SELECT COUNT(0) FROM AREA WHERE PARENT_AREA_ID = @PARENT_AREA_ID";

            SqlParameter[] cmdParms = new SqlParameter[1];
            int i = -1;

            i++;
            cmdParms[i] = new SqlParameter("@PARENT_AREA_ID", SqlDbType.Int, 4);
            cmdParms[i].Direction = ParameterDirection.Input;
            cmdParms[i].Value = parentID;

            object obj = SqlHelper.ExecuteScalar(ConnectionString, CommandType.Text, cmdText, cmdParms);
            int result = 0;
            if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
            {
                result = 0;
            }
            else
            {
                result = int.Parse(obj.ToString());
            }
            return result == 0 ? false : true;
        }

        /// <summary>
        /// 得到地区ID,通areaName模糊查询,取第一条数据,没有找到则返回 0
        /// </summary>
        /// <param name="areaName"></param>
        /// <param name="passport"></param>
        /// <returns></returns>
        public int GetAreaID(string areaName, Security.Passport passport)
        {
            string cmdText = @"SELECT ID,PARENT_AREA_ID FROM AREA WHERE NAME LIKE  '%' + @NAME + '%'";

            SqlParameter[] cmdParms = new SqlParameter[1];
            int i = -1;

            i++;
            cmdParms[i] = new SqlParameter("@NAME", SqlDbType.NVarChar);
            cmdParms[i].Direction = ParameterDirection.Input;
            cmdParms[i].Value = areaName;

            DataSet dsData = SqlHelper.ExecuteDataset(ConnectionString, CommandType.Text, cmdText, cmdParms);

            if (dsData != null && dsData.Tables.Count > 0 && dsData.Tables[0].Rows.Count > 0)
            {
                DataRow[] arrDr = dsData.Tables[0].Select("PARENT_AREA_ID <> 0");
                if (arrDr != null && arrDr.Length > 0)
                {
                    return int.Parse(arrDr[0]["ID"].ToString());
                }
                return int.Parse(dsData.Tables[0].Rows[0]["ID"].ToString());
            }
            return 0;
        }
        /// <summary>
        /// 通过站点代码获得所属城市
        /// </summary>
        /// <param name="siteCode"></param>
        /// <param name="passport"></param>
        /// <returns></returns>
        public DataTable GetAreaByCode(string siteCode, Security.Passport passport)
        {
            string cmdText = @"SELECT * FROM AREA WHERE SITE_CODE=@SITE_CODE";
            SqlParameter[] cmdParms = new SqlParameter[1];
            int i = -1;

            i++;
            cmdParms[i] = new SqlParameter("@SITE_CODE", SqlDbType.NVarChar);
            cmdParms[i].Direction = ParameterDirection.Input;
            cmdParms[i].Value = siteCode;

            return SqlHelper.ExecuteDataset(ConnectionString, CommandType.Text, cmdText, cmdParms).Tables[0];
        }
        /// <summary>
        /// 分站代码是否存在
        /// </summary>
        /// <param name="siteCode"></param>
        /// <param name="passport"></param>
        /// <returns></returns>
        public bool ExistsSiteCode(string siteCode,int areaID ,Security.Passport passport)
        {
            string cmdText = @"SELECT count(0) FROM AREA WHERE SITE_CODE=@SITE_CODE AND ID <> @ID";
            SqlParameter[] cmdParms = new SqlParameter[2];
            int i = -1;

            i++;
            cmdParms[i] = new SqlParameter("@SITE_CODE", SqlDbType.NVarChar);
            cmdParms[i].Direction = ParameterDirection.Input;
            cmdParms[i].Value = siteCode;

            i++;
            cmdParms[i] = new SqlParameter("@ID", SqlDbType.Int);
            cmdParms[i].Direction = ParameterDirection.Input;
            cmdParms[i].Value = areaID;

            object obj = SqlHelper.ExecuteScalar(ConnectionString, CommandType.Text, cmdText, cmdParms);
            int result = 0;
            if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
            {
                result = 0;
            }
            else
            {
                result = int.Parse(obj.ToString());
            }
            return result == 0 ? false : true;
        }
        #endregion
    }
}
